/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.sun.courier.dal;

import com.sun.courier.dto.Courier_CourierManageDTO;
import com.sun.courier.entity.Courier;
import com.sun.courier.entity.CourierManage;
import com.sun.courier.model.CourierModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HONG QUAN
 */
public class CourierDAL {
    DataAccessHelper dah = new DataAccessHelper();
    
    public CourierDAL() {
    }

    public ArrayList<Courier> loadAllCourier(){
        ArrayList<Courier> courierLst = new ArrayList<>();
        String strQuery = "Select * from Courier";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                courierLst.add(c);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
       return courierLst;
    }
    
    public Boolean updateCourier(Courier courier){
        int rs = 0;
        String strQuery = "Update Courier set CourierName='"+courier.getCourierName()+"',CustomerName='"+courier.getCustomerName()+"',Phone='"+courier.getPhone()+"',Status="
                + "'"+courier.getStatus()+"',Weight='"+courier.getWeight()+"',DepartLocation='"+courier.getDepartLocation()+"',Destination='"+courier.getDestination()+"',Distance='"+courier.getDistance()+"'"
                + "where CourierId ='"+courier.getCourierId()+"'";
        try {
            rs = dah.executeUpdate(strQuery);
        } catch (Exception e) {
        }
        if(rs>0){
            return true;
        }
        return false;
    }
    
    public Boolean insertCourier(Courier c){
        int rs = 0;
        String strQuery = "Insert into Courier values('"+c.getCourierName()+"','"+c.getCustomerName()+"','"+c.getReceivingCustomer()+"','"+c.getPhone()+"','"+c.getStatus()+"','"+c.getWeight()+"','"+
                c.getDepartLocation()+"','"+c.getDestination()+"','"+c.getDistance()+"')";
        try {
            rs = dah.executeUpdate(strQuery);
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(rs>0){
            return true;
        }
        return false;
    }
    
    public ArrayList<Courier> getLastId(){
        ArrayList<Courier> cIdLst = new ArrayList<>();
        
        String strQuery = "Select top 1 CourierId from Courier  order by CourierId desc";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                c.setCourierId(rs.getInt("CourierId"));
                cIdLst.add(c);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return cIdLst;
    }
    
    public int countRowCourier(){
        int kq = 0; 
        String strQuery = "Select Count(CourierId) from Courier";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                kq = Integer.parseInt(rs.getString(0));
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return kq;
    }
    
    public Boolean deleteCourier(int courierId){
        int rs = 0;
        String strQuery = "Delete from Courier where CourierId ='"+courierId+"'";
        try {
            rs = dah.executeUpdate(strQuery);
        } catch (Exception e) {
        }
        if(rs > 0){
            return true;
        }
        return false;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCourierToReturnCustomer(int empId){
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,CourierManage.Status,\n" +
"            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage\n" +
"					on Courier.CourierId = CourierManage.CourierId\n" +
"					join Employee on CourierManage.Receiver = Employee.EmpId\n" +
"					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
"					where (CourierManage.Status like '10') and CourierManage.CurrentPosition like '1' and Employee.EmpId='"+empId+"'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public String getEmployeeName(int EmployeeId){
        String strQuery = "Select EmployeeName from Employee where EmpId='"+EmployeeId+"'";
        String employeeName = null;
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                employeeName = rs.getString("EmployeeName");
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return employeeName;
    }
 
    public int getEmployeeIdByUsername(String username){
        int employeeId = -1;
        String strQuery = "Select EmpId from EmployeeLogin where Username ='"+username+"'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                employeeId = rs.getInt("EmpId");
            }
        } catch (Exception e) {
        }
        return employeeId;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCoouriersWillReceiveFromColleagues(int empId){
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Receiver = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '5') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId ='"+empId+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCouriersReceivedFromCustomer(int EmpId, String currentDay){
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Receiver = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '1') and (CourierManage.CurrentPosition like '1' or CourierManage.CurrentPosition like '0') and EmployeeLogin.EmpId ='"+EmpId+"' and CourierManage.UpdateTime='"+currentDay+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCouriersReceivedFromCustomer(int empId){
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Receiver = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '1') and (CourierManage.CurrentPosition like '1'or CourierManage.CurrentPosition like '0') and EmployeeLogin.EmpId ='"+empId+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCouriersReturnedToCustomer(int empId){//Chua Sender Thanh Receiver
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Receiver = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '10') and CourierManage.CurrentPosition like '0' and EmployeeLogin.EmpId ='"+empId+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCourierSentToStore(int empId){//Chua Sender Thanh Receiver
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Sender = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '2') and CourierManage.CurrentPosition like '0' and EmployeeLogin.EmpId ='"+empId+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier_CourierManageDTO> loadCourierReceivedStore(int empId){//Chua Sender Thanh Receiver
        String strQuery = "Select * from Courier join CourierManage \n" +
                                        "on Courier.CourierId = CourierManage.CourierId \n" +
                                        "join Employee on CourierManage.Receiver = Employee.EmpId\n" +
                                        "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                                        "where (CourierManage.Status like '5') and CourierManage.CurrentPosition like '0' and EmployeeLogin.EmpId ='"+empId+"'";
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setDispatcher(rs.getInt("Dispatcher"));
                cm.setStatus(5);
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);
                
                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return lst;
    }
    
    public ArrayList<Courier> getCourierInformationById(int CourierId){
        ArrayList<Courier> lst = new ArrayList<>();
        String strQuery = "Select * from Courier where CourierId='"+CourierId+"'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Courier c = new Courier();
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                c.setStatus(rs.getString("Status"));
                c.setWeight(rs.getString("Weight"));
                c.setDepartLocation(rs.getString("DepartLocation"));
                c.setDestination(rs.getString("Destination"));
                c.setDistance(rs.getString("Distance"));
                lst.add(c);
            }
        } catch (Exception e) {
        }
        return lst;
    }
    
    ////////////////////////////////////////////NAMBH////////////////////////////////////
    private final static String GET_ALL = "select * from Courier ORDER BY CourierId desc";
    private final static String SEARCH_COURIERNAME = "select * from Courier where CourierName like ? "
            //            + "or CustomerName like ? "
            //            + "or Phone like ? "
            //            + "or DepartLocation like ? "
            //            + "or Destination like ? "
            + "ORDER BY CourierId desc";
    private final static String SEARCH_CUSTEMERNAME = "select * from Courier where CustomerName like ? "
            + "ORDER BY CourierId desc";
    private final static String SEARCH_PHONE = "select * from Courier where Phone like ? "
            + "ORDER BY CourierId desc";
//    private DataAccessHelper dah = new DataAccessHelper();
    private Connection con;

    public ArrayList<Courier> getAll() {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                ResultSet rs = con.prepareStatement(GET_ALL).executeQuery();
                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

    public ArrayList<Courier> searchCourierName(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_COURIERNAME);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

    public ArrayList<Courier> searchCustemer(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_CUSTEMERNAME);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

    public ArrayList<Courier> searchPhone(String strSeach) {
        ArrayList<Courier> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                PreparedStatement pre = con.prepareStatement(SEARCH_PHONE);
                pre.setString(1, strSeach);
//                pre.setString(2, "%"+strSeach+"%");
//                pre.setString(3, "%"+strSeach+"%");
//                pre.setString(4, "%"+strSeach+"%");
//                pre.setString(5, "%"+strSeach+"%");
                ResultSet rs = pre.executeQuery();

                try {
                    while (rs.next()) {
                        Courier obj = new Courier();
                        obj.setCourierId(rs.getInt("CourierId"));
                        obj.setCourierName(rs.getString("CourierName"));
                        obj.setCustomerName(rs.getString("CustomerName"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setStatus(rs.getString("Status"));
                        obj.setWeight(rs.getString("Weight"));
                        obj.setDepartLocation(rs.getString("DepartLocation"));
                        obj.setDestination(rs.getString("Destination"));
                        obj.setDistance(rs.getString("Distance"));


                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }
}
